import logging

import pymysql


class DButils(object):
    __conn = None
    @classmethod
    def __connect_db(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host="211.103.136.244",port=7068,user="student",password="KDTX_student_202304",database="huike",charset="utf8")
        return cls.__conn

    @classmethod
    def __close_db(cls):
        # 关闭连接
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

    @classmethod
    def query_db(cls,sql):
        cursor = None
        result = None
        try:
            conn = cls.__connect_db()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = cursor.fetchall()
        except Exception as e:
            logging.info(e)
        finally:
            if cursor:
                cursor.close()
            cls.__close_db()
            return result

    @classmethod
    def uid_db(cls,sql):
        cursor = None
        result = None
        try:
            conn = cls.__connect_db()
            cursor = conn.cursor()
            cursor.execute(sql)
            cls.__conn.commit()
        except Exception as e:
            logging.info(e)
            cls.__conn.rollback()
        finally:
            if cursor:
                cursor.close()
            cls.__close_db()

if __name__ == '__main__':
    sql1="select name from tb_course"
    logging.info(DButils.query_db(sql1))

    sql1_5="delete from tb_course where name='零基础学IT就来黑马程序员'"
    DButils.uid_db(sql1_5)

    sql2="insert into tb_course (name) values ('零基础学IT就来黑马程序员')"
    DButils.uid_db(sql2)

    sql3="update tb_course set id = 1  where name = '零基础学IT就来黑马程序员'"
    DButils.uid_db(sql3)

    sql4="select name from tb_course"
    logging.info(DButils.query_db(sql4))